SELECT 
        provider.id, 
        provider.avatar, 
        CONCAT(provider.name, ' - Credit: $', IFNULL(cre.used_credit, 0), '/$', IFNULL(cre.available_credit, 0)) AS displayText,
        CONCAT(provider.name, ' - Credit Limit: $', IFNULL(cre.used_credit, 0), '/ Credit Usage: $', IFNULL(cre.available_credit, 0)) AS displayTitle,
        JSON_OBJECT(
            'id_provider', provider.id,
            'provider_name', provider.name,
            'bill_to_address', provider.bill_to_address, 
            'bill_to_city', provider.bill_to_city, 
            'bill_to_state', provider.bill_to_state, 
            'bill_to_zip', provider.bill_to_zip, 
            'ship_to_address', provider.ship_to_address, 
            'ship_to_city', provider.ship_to_city, 
            'ship_to_state', provider.ship_to_state,
            'ship_to_zip', provider.ship_to_zip,
            'email', provider.email,
            'available_credit', IFNULL(cre.available_credit, 0)
        ) AS otherField,
        JSON_OBJECT('profit_star', provider_behaivor.profit_star, 'roi_star', provider_behaivor.roi_star) AS behaivor
    FROM provider
    LEFT JOIN provider_behaivor ON provider_behaivor.provider_id = provider.id 
    LEFT JOIN (
        SELECT 
            combined.provider_id,
            ROUND(SUM(
                (combined.general_total_price + combined.spent_credit) - 
                (combined.paid_amount + combined.credit_amount)
            ), 2) AS total_due
        FROM (
            SELECT i.provider_id, i.general_total_price, i.paid_amount, i.spent_credit, i.credit_amount, i.payment_status, i.deleted
            FROM purchase AS i
            WHERE i.payment_status NOT IN ('paid','credited') AND i.deleted = '0'
            UNION ALL
            SELECT o.provider_id, o.amount AS general_total_price, o.paid_amount, 0, 0, o.payment_status, o.deleted
            FROM purchase_vs_expenses AS o
            WHERE o.payment_status != 'paid' AND o.deleted = '0'
        ) AS combined
        GROUP BY combined.provider_id
    ) d ON d.provider_id = provider.id
    LEFT JOIN (
        SELECT 
            pc.provider_id,
            SUM(pc.amount) - COALESCE(SUM(pvp.sent_amount), 0) AS available_credit,
            COALESCE(SUM(pvp.sent_amount), 0) AS used_credit
        FROM providercredit pc
        LEFT JOIN providercredit_vs_paid_purchase pvp ON pc.id = pvp.providercreditId AND pvp.deleted = '0'
        WHERE pc.deleted = 0
        GROUP BY pc.provider_id
    ) cre ON cre.provider_id = provider.id
    WHERE provider.deleted = '0'  AND (name LIKE LOWER('%%') ) 
    ORDER BY provider.id LIMIT 10;